The Tablespace I/O and File I/O Statistics Reports are shown above.

If the Tablespace I/O Report seems to indicate a tablespace has I/O problems, we can then use the File I/O Statistics report to drill into the datafiles of the tablespace in question

You will see statistics like Average number of read requests per second, Average read time in milliseconds, write waits, number of buffer waits and average wait time in milliseconds for buffer waits.

• Av Reads/s - average number of read requests per second.
• Av Rd(ms) - average read time in ms.
• Av Blks/Rd - average number of blocks per read.
• Buffer Waits - number of buffer busy waits.
• Av Buf Wt(ms) - average wait time in milliseconds for buffer busy waits.

These values indicate read and write operations of ARCH, Buffer Cache Reads, Data Pump, DBWR, Direct Reads, Direct Writes, LGWR, RMAN, Recovery, Smart Scan, Streams AQ, XDB, and Others.

If Buffer Waits for a tablespace is greater than 1000, you may want to consider tablespace reorganization in order to spread tables within it across another tablespaces.

Av Rd(ms) columns must not exceed 20 otherwise it worths involving your OS team and hardware vendor to investigate the I/O bottleneck.

If a datafile consistently has average read times of 20 ms or greater then:

1. The queries against the contents of the owning tablespace should be examined and tuned so that less data is retrieved.
2. If the tablespace contains indexes, another option is to compress the indexes so that they require less space and hence, less IO.
3. The contents of that datafile should be redistributed across several disks/logical volumes to more easily accommodate the load.
4. If the disk layout seems optimal, check the disk controller layout. It may be that the datafiles need to be distributed across more disk sets.

This is also an important metic, but should be combined with Av Blk per Rd. We can have single block reads in the file, and we can have a little amount of them which will increase our Av Rd/ms but it s not a real problem always:

• Idle files should be ignored because they can produce high values for I/O due to disk spinup and etc.
• We can have multiblock read at a time, it is threaded like a single block I/O. That is we start the timer, issue one or more blocks, we stop the timer.
• Av Buf Wt(ms) is important as in a system with low DML activity and a well configured buffer cache and I/O susbystem, there should be almost zero buffer waits.
• Buffer wait helps us see the how much sessions/processes have to wait for a buffer which is read into the buffer cache.

After such an analysis, if you decide you have an I/O problem, consider doing the following:

• Reduce the I/O requirements of the database by tuning instance parameters
• Optimize I/O at the OS level
• Balancing the database I/O by usage of Oracle ASM
• Balancing the database I/O by usage of Striping, RAID, SAN or NAS
• Redistribute database I/O by manual placement of database files across different filesystems, controllers and physical devices
• Reduce the data volumes of the current database by archving data
• Investing in more and/or faster hardware.